Forecasting deposit stickyness in custodian banking institutions

Project Description:

I currently work for a large custody bank that as most other organizations are dealing with uncertainty driven by the Covid -19 epidemic. Unlike many other retail-oriented services we experience deposit growth during periods of stress. The question that arises in all of our executive team meetings is if the deposits will stay on, and if not how long will the duration of elevated deposits be. The importance to my field of work is determining how will those coming deposit actions of our clients will influence the liquidity position of the bank, and how we need to manage preemptively for those impending actions. This project leveraging historical balance sheet information available from custodian banking institutions, will forecast ramp up and ramp down inflection points for deposit growth and subsequent decline using various macroeconomic indicators as independent variables in an effort to get a sense of what’s to come for our bank's deposit behavior.

Data Science Task:

To determine and apply the most appropriate supervised learning algorithm to forecast deposit inflection points during economic stress, in an effort to forecast current deposit trends at my banking institution

Data:

This project will take historical balance sheet information, specifically foreign office, and domestic deposits from some of the large custody banks including Northern Trust, Bank of New York, and State Street Bank & Trust using publicly available balance sheet information from SNL or via the Edgar API tool, and then obtain historical macroeconomic indicators from FRED, including interest rates, stock indices like Dow Jones S&P, GDP Growth, CPI, VIX unemployment and treasury yield in an effort to forecast the inflection point in deposit growth and subsequent declines resulting from periods of economic stress.

For this effort I will be looking at quarterly banking deposit data as my dependent variable, with a goal of going back as far as possible with historical data I was unable to capture the 1987 black Monday, but did get the dot com bust of the 90's. This data was obtained via S&P’s SNL. My independent variables were obtained via FRED pretty heavily to obtain the most up to date historical macroeconomic indicator information, and yahoo finance to capture S&P as well as [3].

Data Analysis:

Random fores regression was applied to handle non-normal distribution of deposits across all banks

First steps - Independent Data Step:

We will obtain all the necesary data for our independent variables, whose sources are largely FRED and Yahoo Finance for the Dow Jones and S&P 500 data [6][7]

Next Step: Dependent Variables

After a come to Jesus moment, realizing that the FDIC Bankfind API did not contain any of the needed balance sheet information that I required; and determining that a good deal of people that work with the Edgar API to parse balance sheet information spend months and or all of their free time to get a single data point for their needs - I decided that this route of data capture while cool and a little flashy was way too involved of a project than I could bite off in the next coming weeks.

I pivoted my approach, while my SNL add on at the office was not functioning appropriately I did remember that I have an S&P Market Intelligence login for peer comparisons. I used their MI Report builder, while clunky got the job done. I was able to pull down excel balance sheet information for Northern Trust, State Street Bank & Trust, and Bank of New York Mellon information in 40 quarter lots from 1Q 1990 to 3Q 2020.

Since it was all in excel, I tacked this into the output file from my cleaned independent variable data from above, providing a clean dataset with all needed data points. I will be limited to June 2020 which is the most recent period available on S&P unfortunately. However with March through June data we will be able to see the deposit ramp up that many custodian banking institutions witnessed as a result of the market uncertainty and other macro factors that we're not quite sure are at this point yet; as we're speculating that supranational funds are ramping up funds for Covid 19 initiaitves - however no hard correlations of this concept have been fleshed out at this point in time.

Enough of the talk, below is an upload of the complete data set "Final Data."

We will start by calling info to understand the data within the dataframe:

Exploratory Data Analysis

Looking at our BONY domestic deposits heatmap we can see that our more saturated correlations include: Dow Jones Close (djii_close_x) S&P 500 Close (AdjClose) GDP Case schiller home index (CS_Index) Consumer Price Index (CPIAUCSL)

So we can investigate the BONY deposits further using those independent variables.

Looking at our BONY foreign office deposits heatmap we can see that our more saturated correlations, which are mostly negative include: VIX (VIXCLS) Dow Jones Close (djii_close_x) 10-year Treasury Yeild (10yrTreasYeild) S&P 500 Close (AdjClose) GDP Case schiller home index (CS_Index) Consumer Price Index (CPIAUCSL)

So we can investigate the BONY deposits further using those independent variables.

Looking at our BONY Fed Fund deposits heatmap we can see that our more saturated correlations, which are mostly negative include:

Dow Jones Close (djii_close_x) 10-year Treasury Yeild (10yrTreasYeild) S&P 500 Close (AdjClose) GDP Case schiller home index (CS_Index) Fed Funds Rate (FEDFUNDS) Consumer Price Index (CPIAUCSL)

So we can investigate the BONY deposits further using those independent variables

Surprisingly SSB&T domestic deposits are not highly correlated to any of the macro factors that I investigated. Due to this I will skip out analysis on what moves SSB&T domestic deposits.

For SSB&T foreign office deposits do have some higher level corrrelations that we can investigate, including: 10-Year Treasury Yeild GDP CS_Inex Fed Funds Rate CPI

For SSB&T Fed Funds deposits have stronger corrrelations that we can investigate, including: djii close adj_close_x - S&P 10-Year Treasury Yeild GDP CS_Index Fed Funds Rate CPI

For Northern Trust we have some stronger correlations on Domestic deposits and can investigate: DJI Close S&P Close GDP Case Schiller Index Fed Funds Rate CPI

Similar to BONY, Northern Trust has a greater proportion of negatively correlated variables, but in this scenario since I know that these account for the buik of deposits we will investigate the following variables: Djii Close 10-year treasury yeild S&P500 close GDP Case Schiller Fed Funds CPI

These time series plots might be interesting plotted against each other by institution. Lets give that a try:

Domestic Deposits by Bank:

Foreign Office Deposits by Bank:

FFP & Repo by Bank:

What if we transform data into quarterly averages???

We can resample based on the date and transform into quarterly data, across the board there are some independent variables that have no bearing on the correlations within the data. Now we will transform, and strip out some of the data that does not seem to have much impact on our dependent variables we will transform our dataframe and re-run some of this analysis to see if there are any material changes in the correlations.

In addition knowing that our FFP and Repo deposits are likely structural and not a strong indication of client behavior based on macroeconomic factors we can remove the FFP analysis from our views for this 2nd round of descriptive analysis with quarterly data.

Pretty strong correlations across the board for BONY domestic deposits, with the exception of VIX.

Similar to last set of results, negative correlation across the boad with our BONY Foreign Office deposits, we can keep with the same analysis using this quarterly data.

Showing much weaker correlations with the quarterly adjusted data for SSB&T domestic - with only our stock indices close change being the strongest correlations.

Stronger correlations across the board for Domestic deposits with quarterly adjusted data, VIX still not a strong indicator.

Again stronger correlations with the quarterly data set, we will utilize all of our independent variables in our analysis on foreign office deposits.

Certainly a weirder distribution on this view, we will have to give this some more consideration in our choice of algorithm for forecasting.

A second view of quarterly deposits by bank:

Thoughts dependent variable distributions and appropriate algorithm usage:

A good deal of the dependent variables in our analysis would likely fall into the category where 75% of the data would fall within two standard deviations; or 89% falling within 3 standard deviations with the exception of the SSB&T FFP deposits. Since these deposits are not necessarily and indicator of client behavior and are more structural in nature, it may make sense to strip the FFP & Repo from our analysis at this point.

The non-linear nature of our data application of the Chebyshev bound may be a good approach to the remainde of the analysis due to the factor that our data would not necessarily fall into a normal distribution[8], we could also consider leveraging the Chernoff Bound/Hoeffding Distribution for this analysis.

Some other appraoches could be stick with using non-linear algorithms, including support vector regression, k-nearest neighbors and extra tree. In addition to the non-linear we could also investigate performance on ensemble algorithms like Gradient Boosting Machines and Random Forest.

With some reading I feel like selecting a non-Linear regression tree using Scikit-learn would be a good appraoach for this time series forecasting exercise

Subset names for each bank BONYDom2 BONYFo2 SSBTDom2 SSBTFo2 NTRSDom2 NTRSFo2

BONY Domestic Depoists Analysis:

The first line of code creates an object of the target variable called 'target_column_BD'. The second line gives us the list of all the features, excluding the target variable 'BONYDom'.

We have seen above that the units of the variables differ significantly and may influence the modeling process. To prevent this, we will do normalization via scaling of the predictors between 0 and 1. The third line performs this task.

The fourth line displays the summary of the normalized data. We can see that all the independent variables have now been scaled between 0 and 1. The target variable remains unchanged. [8]

We will build our model on the training set and evaluate its performance on the test set. The first couple of lines of code below create arrays of the independent (X) and dependent (y) variables, respectively. The third line splits the data into training and test dataset, with the 'test_size' argument specifying the percentage of data to be kept in the test data. The fourth line prints the shape of the training set[11]

CART Feature Importance Analysis: We will first investigate feature importance for the CART models that will be leveraged for this analysis. This analysis introduced by Jason Brownlee will be leveraged [12]

We can conduct our analysis based on the feature importance, we can first run the model as is and make adjustments as we see fit. We will start our model selection by viewing the performance of decision trees. I'm having a hard time accepting that Vix is that important to this model.

Once the model is built on the training set, we can make the predictions. The first line of code below predicts on the training set. The second and third lines of code prints the evaluation metrics - RMSE and R-squared - on the training set. The same steps are repeated on the test dataset in the fourth to sixth lines.[10]

The above output shows that the RMSE is 352.38 for train data and 325.17 for test data. On the other hand, the R-squared value is 72 percent for train data and 80 percent for test data. More improvement can be done by parameter tuning. We will be changing the values of the parameter, 'max_depth', to see how that affects the model performance[11].

The first four lines of code below instantiates and fits the regression trees with 'max_depth' parameter of 2 and 5, respectively. The fifth and sixth lines of code generate predictions on the training data, whereas the seventh and eight lines of code gives predictions on the testing data [11].

Generate the evaluation metrics - RMSE and R-squared - for the first regression tree, 'dtree1Bd'.

Not the best results when adjusting the max depth of our decision tree to 5, we will change our approach and try Random forest on the BONY domestic deposits to see if we get a better outcome of our model.

The first line of code below instantiates the Random Forest Regression model with the 'n_estimators' value of 500. 'n_estimators' indicates the number of trees in the forest. The second line fits the model to the training data.

The third line of code predicts, while the fourth and fifth lines print the evaluation metrics - RMSE and R-squared - on the training set. The same steps are repeated on the test dataset in the sixth to eight lines of code.

Overall our Random Forest Model yeilds better results, our RMSE and R-Squared values on the training data are 153, and 94.7% respectively. For the test data our RMSE and R-Squared yeilds 252 and 88% respectively, much better results versus the decision trees used before - showing that it is a superior model as compared to decision trees for forecasting.

Lets quickly see if by reducing the explanatory variables down to those with the highest feature scores yeilds even better results. We will make a new subset of the BONY Domestic deposits, reducing it down to the features with the most importance from our earlier investigation of classification feature importnace and see how our peformance stacks up against our model.

Reducing our features did not yeild better results, for BONY Domestic Deposits. So now we can focus on our first Random Forest model and view performance metrics.

Our RMSE on the training set was 153mm with a R-Squared value is 95%, the best possible score is 1.0 or 100% so this model is a good one for BONY Domestic Deposits. Below lets get a view of our precitions

To visualize a single decision tree for the BONY Domestic deposit model we can select one tree and save the tree as an image using the script below:

BONY Foreign Office Deposit Analysis:

We will implement our random forest regression on the BONY Foreign Office deposits

In our BONY Foreign Office deposits we can see that our features play a more important role across the board, with the exception of 4 which is our S&P 500 Adjusted Close. We will move forward with this analysis.

Now we can run our random forest regression.

Overall the RMSE and R-squared error for the training data is 53mm and 96%, and on the testing data we have slightly lower lesser accuracy in thatour RMSE is 851, and R-squared is 68%, lower than our domestic analysis.

Given the fact that my main concern is forecasting NTRS deposits, I'm satisfied with this model we can move forward, for additional accuracy we could strip out the S&P 500 close from our analysis and see if results improve but BONY is not the most important factor in this analysis.

To visualize a single decision tree for the BONY Foreign Office deposit model we can select one tree and save the tree as an image using the script below:

SSBT Domestic Deposits Analysis

For SSBT Domestic deposits it appears that we only have a couple of features within our model that are important, this would include VIX, 10 year Treasury Yeild (kind of), GDP, and Case Schiller Index.

So we will narrow down our dataset to incorporate just these features for our analysis.

Our model yeilded more optimal results with 760mm MSE and 98% on our RMSE on our training data, testing a 247mm MSE and 85% R-Squared, telling us that our subsetted data did a good job of predicting SSBT Domestic Deposits.

Now we can get a view of our predcitions below:

Now we can get a view of our trees:

SSBT Foreign Office Deposit Analysis:

On our SSBT Foreign Office Deposits, features 3,4,5, and 7 have the most importance this is: TEDRATE, AdjClose, and GDP we can adjust our data frame to include just those predictors: AdjClose, GDP, CS_Index, CPIAUCSL

Our RMSE & R-Squared for the testing and training set fall within a resonable level of tolerance. Now we can get a look at our predcitions.

Northern Trust Domestic Deposit Analysis

We will go a little further in depth with this analysis as we'd like to apply this at some level to forecast deposit movement.

It will always surprise me the correlation between VIX and deposits, obviously for NTRS Domestic deposits this plays an important factor. In addition 10-year treasury yield, and CPI. Since there are only three indicators we can run the model as a whole and once more with these features only and see if we yield better results.

Our RMSE & R-Squared for the testing and training set fall within a resonable level of tolerance, 0.009, and 98% and 267mm 0.78. Let's see if adjusting our features to align with our feature importance analysis will improve the peformance of the model.

Ok on our testing and training we have 77mm RMSE and 0.98 on our R-Squared which is very good, our predictions we have 0.02mm RMSE amd 0.77 R Squared, less accurate than our initial version with NTRS2 data. We will rely on our NTRS2 random forest model with better accuracy to forecast our domestic deposits.

Getting a visual on our predictions in blue versus our actuals in green, the random forest model peforms quite effectively in predicting domestic deposit movements.

NTRS Foreign Office Deposits:

Our random forest model and foreign office depoists are good friends! RMSE is good and high r-squared across both testing and training sets.

The results look amazing, proving that random forest regressors are an adequate methodology for forecasting foreign office deposits. It will be pretty cool to see this methodology applied to more granular data to get daily predictions. Overall I'm very statisfied with this approach and look forward to helping apply the methodology for additional forecasting.

Thanks!

References

[1] python-edgar. (2019, November 9). Retrieved from https://pypi.org/project/python-edgar/

[2] Schroeder, J. (2019, August 27). Tutorial: real-time live feed of SEC filings using Python & socket.io. Retrieved from https://medium.com/@jan_5421/crawling-new-filings-on-sec-edgar-using-python-and-socket-io-in-real-time-5cba8c6a3eb8

[3] Chen, K. (2020). Use Python to download TXT-format SEC filings on EDGAR (Part I) | Kai Chen. Retrieved October 29, 2020, from http://kaichen.work/?p=59

[4] A. (2020). amaline/fdic-banks-api-python-client. Retrieved from https://github.com/amaline/fdic-banks-api-python-client

[5] D. (2020b). dpguthrie/bankfind. Retrieved from https://github.com/dpguthrie/bankfind

[6] Federal Reserve Economic Data | FRED | St. Louis Fed. (2020). Retrieved from https://fred.stlouisfed.org/

[7]Yahoo is now a part of Verizon Media. (2020). Retrieved from https://finance.yahoo.com/quote/%5EGSPC/history/

[8]Sarkar, T. (2018, November 5). What if your data is NOT Normal? - Towards Data Science. Medium. https://towardsdatascience.com/what-if-your-data-is-not-normal-d7293f7b8f0

[9] Chaipitakporn, C. (2019, October 26). Illustration with Python: Chebyshev’s Inequality - Analytics Vidhya. Medium. https://medium.com/analytics-vidhya/illustration-with-python-chebyshevs-inequality-b34be151c547

[10] Brownlee, J. (2020, August 28). How to Develop a Framework to Spot-Check Machine Learning Algorithms in Python. Machine Learning Mastery. https://machinelearningmastery.com/spot-check-machine-learning-algorithms-in-python/

[11] Singh, D. (2019, May 21). Non-Linear Regression Trees with scikit-learn. Pluralsight. https://www.pluralsight.com/guides/non-linear-regression-trees-scikit-learn

[12]Brownlee, J. (2020a, August 20). How to Calculate Feature Importance With Python. Machine Learning Mastery. https://machinelearningmastery.com/calculate-feature-importance-with-python/